﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Aspose.Cells;
using System.Data;
using System.Collections;
using System.Drawing;
namespace LCrs.Web
{
    public class OutExcel
    {        
        /// <summary>
        /// 利用Aspose.Cells对DataTable数据生成Excel文件
        /// </summary>
        /// <returns>是否成功</returns>
        /// <param name="response">包含存储路径，直接用Response即可</param>
        /// <param name="dt">数据体</param>
        /// <param name="FileName">文件名</param>
        /// <param name="SheetName">sheet名</param>
        /// <param name="Title">表头</param>
        /// <param name="ColTitle">列标题，字符串数组</param>
        /// <param name="ColName">列名，字符串数组</param>
        /// <param name="ColWidth">列宽，整数数组</param>
        /// <param name="ColStyle">列样式，整数数组，1=居中文本、2=特殊字体Georgia、3=居左文本、4=整数(x)、5=2位小数(x,xxx.xx)、6=日期(yyyy-M-d不带时间)、7=百分数(0.123=12.3%)</param>
        /// <param name="ColTitleRow">列标题的开始行号，有title的设为1即可</param>
        /// <param name="err">返回的错误信息</param>
        public static bool ExportToExcel(DataTable dt,string outfile,ref string err)
		{			
			try
			{
				Workbook workbook = new Workbook(); 

							//string path = System.Web.HttpContext.Current.Server.MapPath("~");
							//path = path.Substring(0, path.LastIndexOf("\\"));		
							//path += @"\template\mb.xls";				
							//workbook.Open(path);
                workbook.Worksheets.Add();
				//打开sheet
				//workbook.Worksheets.Clear();
				Worksheet worksheet = workbook.Worksheets[0];

				Cells cells = worksheet.Cells;
                Style style1 = workbook.Styles[workbook.Styles.Add()];//新增样式  
                //style1.HorizontalAlignment = TextAlignmentType.Center;//文字居中  
                //style1.Font.Name = "宋体";//文字字体  
                //style1.Font.Size = 22;//文字大小  
                //style1.IsLocked = false;//单元格解锁  
                //style1.Font.IsBold = true;//粗体  
                //style1.ForegroundColor = Color.FromArgb(0xaa, 0xcc, 0xbb);//设置背景色  
                //style1.Pattern = BackgroundType.Solid; //设置背景样式  
                style1.IsTextWrapped = true;//单元格内容自动换行 
                
                for (int i=0; i < dt.Columns.Count; i++)
                {
                    cells[0, i].PutValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {

                        if (dt.Rows[i][dt.Columns[j].ColumnName].ToString().Contains("\\n"))
                        {
                            cells[i + 1, j].SetStyle(style1);
                            cells[i + 1, j].PutValue(dt.Rows[i][dt.Columns[j].ColumnName].ToString().Replace("\\n","\n"));
                        }
                        else
                        {
                            cells[i + 1, j].PutValue(dt.Rows[i][dt.Columns[j].ColumnName].ToString());
                        }
                    }
                }
                worksheet.AutoFitColumns();
                cells = worksheet.Cells;
                int columnCount = cells.MaxColumn;  //获取表页的最大列数
                int rowCount = cells.MaxRow;        //获取表页的最大行数

                for (int col = 0; col < columnCount; col++)
                {
                    worksheet.AutoFitColumn(col, 0, rowCount);
                }
                for (int col = 0; col < columnCount; col++)
                {
                    cells.SetColumnWidthPixel(col, cells.GetColumnWidthPixel(col) + 30);
                }
                workbook.Save(outfile,SaveFormat.Excel97To2003);
				return true;
			}
			catch (Exception ex)
			{
				err = ex.Message;
				return false;
			}
		}//end ExportToExcel()
        
    }
}